CREATE TABLE IF NOT EXISTS recipe_templates(
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	title			VARCHAR(100),
	created			DATETIME,
	modified		DATETIME
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS recipes (
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	title			VARCHAR(100),
	description		TEXT,
	content			TEXT,
	image			VARCHAR(100),
	template_id		INT		UNSIGNED	NOT NULL,
	created			DATETIME,
	modified		DATETIME,
	FOREIGN KEY (template_id) REFERENCES recipe_templates(id)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS recipe_categories(
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	title			VARCHAR(100),
	parent_id		INT 	UNSIGNED	NOT NULL,
	created			DATETIME,
	modified		DATETIME,
	FOREIGN KEY (parent_id) REFERENCES recipe_categories(id)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS recipes_recipe_categories(
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	recipe_id		INT		UNSIGNED	NOT NULL,
	category_id		INT		UNSIGNED	NOT NULL,
	created			DATETIME,
	modified		DATETIME,
	FOREIGN KEY (recipe_id) REFERENCES recipes(id),
	FOREIGN KEY (category_id) REFERENCES recipe_categories(id)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS recipe_mains(
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	title			VARCHAR(100),
	created			DATETIME,
	modified		DATETIME
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS recipes_recipe_mains(
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	recipe_id		INT		UNSIGNED	NOT NULL,
	main_id			INT		UNSIGNED	NOT NULL,
	created			DATETIME,
	modified		DATETIME,
	FOREIGN KEY (recipe_id) REFERENCES recipes(id),
	FOREIGN KEY (main_id) REFERENCES recipe_mains(id)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS general_categories(
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	title			VARCHAR(100),
	parent_id		INT		UNSIGNED	NOT NULL,
	created			DATETIME,
	modified		DATETIME,
	FOREIGN KEY (parent_id) REFERENCES general_categories(id)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS general_tags(
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	title			VARCHAR(100),
	created			DATETIME,
	modified		DATETIME
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS videos (
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	title			VARCHAR(100),
	description		TEXT,
	link			TEXT,
	created			DATETIME,
	modified		DATETIME
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS general_categories_videos (
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	video_id		INT		UNSIGNED	NOT NULL,
	category_id		INT		UNSIGNED	NOT NULL,
	created			DATETIME,
	modified		DATETIME,
	FOREIGN KEY (video_id) REFERENCES videos(id),
	FOREIGN KEY (category_id) REFERENCES general_categories(id)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS general_tags_videos (
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	video_id		INT		UNSIGNED	NOT NULL,
	tag_id			INT		UNSIGNED	NOT NULL,
	created			DATETIME,
	modified		DATETIME,
	FOREIGN KEY (video_id) REFERENCES videos(id),
	FOREIGN KEY (tag_id) REFERENCES general_tags(id)
)ENGINE=INNODB;


CREATE TABLE IF NOT EXISTS articles (
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	title			VARCHAR(100),
	description		TEXT,
	content			TEXT,
	created			DATETIME,
	modified		DATETIME
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS articles_general_categories (
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	article_id		INT		UNSIGNED	NOT NULL,
	category_id		INT		UNSIGNED	NOT NULL,
	created			DATETIME,
	modified		DATETIME,
	FOREIGN KEY (article_id) REFERENCES articles(id),
	FOREIGN KEY (category_id) REFERENCES general_categories(id)
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS article_general_tags (
	id				INT		UNSIGNED	AUTO_INCREMENT		PRIMARY KEY,
	article_id		INT		UNSIGNED	NOT NULL,
	tag_id			INT		UNSIGNED	NOT NULL,
	created			DATETIME,
	modified		DATETIME,
	FOREIGN KEY (article_id) REFERENCES articles(id),
	FOREIGN KEY (tag_id) REFERENCES general_tags(id)
)ENGINE=INNODB;

